﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spMSX_update_backup_msx_server]
#-- Purpose:		Copies data from the primary MSX core tables to a backup
#--					MSX server.  Requires a linked server.
#--	Last Update:	04/28/2016
#--					For a complete history - please review comments in Version
#--					Control.
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spMSX_update_backup_msx_server]
(
	@msx_server_name		sysname
)
AS

SET NOCOUNT ON

--- Declare Local Variables
PRINT 'Just using peer-to-peer replication for this now.'
--DECLARE		@sSQL varchar(8000)

--IF @msx_server_name <> @@SERVERNAME
--  BEGIN
--	SELECT		@sSQL = 'SET IDENTITY_INSERT [dbo].[tblMSX_sql_version] ON

--;MERGE [dbo].[tblMSX_sql_version] AS Target
-- USING ' + QUOTENAME(@msx_server_name) + '.' + QUOTENAME(DB_NAME()) + '.[dbo].[tblMSX_sql_version] AS Source
--	ON Target.ver_id = Source.ver_id

-- WHEN MATCHED AND NOT (
--		Target.ver_major_ro = Source.ver_major_ro
--		AND Target.ver_minor_ro = Source.ver_minor_ro
--		AND Target.ver_description = Source.ver_description
--	) THEN
--		UPDATE SET	Target.ver_major_ro = Source.ver_major_ro,
--					Target.ver_minor_ro = Source.ver_minor_ro,
--					Target.ver_description = Source.ver_description
--WHEN NOT MATCHED BY TARGET THEN
--	INSERT (ver_id, ver_major_ro, ver_minor_ro, ver_description)
--	VALUES (Source.ver_id, Source.ver_major_ro, Source.ver_minor_ro, Source.ver_description)
--WHEN NOT MATCHED BY SOURCE THEN
--	DELETE;

--SET IDENTITY_INSERT [dbo].[tblMSX_sql_version] OFF'
--	EXEC		(@sSQL)

--	SELECT		@sSQL = 'SET IDENTITY_INSERT [dbo].[tblMSX_server_discovery] ON

--;MERGE [dbo].[tblMSX_server_discovery] AS Target
-- USING ' + QUOTENAME(@msx_server_name) + '.' + QUOTENAME(DB_NAME()) + '.[dbo].[tblMSX_server_discovery] AS Source
--	ON Target.ssd_id = Source.ssd_id

-- WHEN MATCHED AND NOT (
--		Target.ssd_is_disabled = Source.ssd_is_disabled
--		AND Target.ssd_last_update = Source.ssd_last_update
--		AND ISNULL(Target.ssd_last_error, ''1/1/1900'') = ISNULL(Source.ssd_last_error, ''1/1/1900'')
--		AND Target.ssd_date_discovered = Source.ssd_date_discovered
--		AND Target.ssd_server_name = Source.ssd_server_name
--		AND ISNULL(Target.ssd_instance_name, SPACE(0)) = ISNULL(Source.ssd_instance_name, SPACE(0))
--		AND ISNULL(Target.ssd_description, SPACE(0)) = ISNULL(Source.ssd_description, SPACE(0))
--		AND ISNULL(Target.ssd_sql_service, SPACE(0)) = ISNULL(Source.ssd_sql_service, SPACE(0))
--		AND ISNULL(Target.ssd_agent_service, SPACE(0)) = ISNULL(Source.ssd_agent_service, SPACE(0))
--		AND ISNULL(Target.ssd_sql_start_mode, SPACE(0)) = ISNULL(Source.ssd_sql_start_mode, SPACE(0))
--		AND ISNULL(Target.ssd_sql_state, SPACE(0)) = ISNULL(Source.ssd_sql_state, SPACE(0))
--		AND ISNULL(Target.ssd_agent_start_mode, SPACE(0)) = ISNULL(Source.ssd_agent_start_mode, SPACE(0))
--		AND ISNULL(Target.ssd_agent_state, SPACE(0)) = ISNULL(Source.ssd_agent_state, SPACE(0))
--		AND Target.ssd_deploy_msx_backup = Source.ssd_deploy_msx_backup
--		AND Target.ssd_deploy_msx_index = Source.ssd_deploy_msx_index
--		AND ISNULL(Target.ssd_app_id, -1) = ISNULL(Source.ssd_app_id, -1)
--	) THEN
--		UPDATE SET	Target.ssd_is_disabled = Source.ssd_is_disabled,
--					Target.ssd_last_update = Source.ssd_last_update,
--					Target.ssd_last_error = Source.ssd_last_error,
--					Target.ssd_date_discovered = Source.ssd_date_discovered,
--					Target.ssd_server_name = Source.ssd_server_name,
--					Target.ssd_instance_name = Source.ssd_instance_name,
--					Target.ssd_description = Source.ssd_description,
--					Target.ssd_sql_service = Source.ssd_sql_service,
--					Target.ssd_agent_service = Source.ssd_agent_service,
--					Target.ssd_sql_start_mode = Source.ssd_sql_start_mode,
--					Target.ssd_sql_state = Source.ssd_sql_state,
--					Target.ssd_agent_start_mode = Source.ssd_agent_start_mode,
--					Target.ssd_agent_state = Source.ssd_agent_state,
--					Target.ssd_deploy_msx_backup = Source.ssd_deploy_msx_backup,
--					Target.ssd_deploy_msx_index = Source.ssd_deploy_msx_index,
--					Target.ssd_app_id = Source.ssd_app_id

--WHEN NOT MATCHED BY TARGET THEN
--	INSERT (ssd_id, ssd_is_disabled, ssd_last_update, ssd_last_error, ssd_date_discovered, 
--			ssd_server_name, ssd_instance_name, ssd_description, ssd_sql_service, ssd_agent_service, 
--			ssd_sql_start_mode, ssd_sql_state, ssd_agent_start_mode, ssd_agent_state,
--			ssd_deploy_msx_backup, ssd_deploy_msx_index, ssd_app_id)
--	VALUES (Source.ssd_id, Source.ssd_is_disabled, Source.ssd_last_update, Source.ssd_last_error, Source.ssd_date_discovered, 
--			Source.ssd_server_name, Source.ssd_instance_name, Source.ssd_description, Source.ssd_sql_service, Source.ssd_agent_service, 
--			Source.ssd_sql_start_mode, Source.ssd_sql_state, Source.ssd_agent_start_mode, Source.ssd_agent_state,
--			Source.ssd_deploy_msx_backup, Source.ssd_deploy_msx_index, Source.ssd_app_id)
--WHEN NOT MATCHED BY SOURCE THEN
--	DELETE;

--SET IDENTITY_INSERT [dbo].[tblMSX_server_discovery] OFF'
--	EXEC		(@sSQL)

--	SELECT		@sSQL = 'SET IDENTITY_INSERT [dbo].[tblMSX_server_vendor_managed] ON

--;MERGE [dbo].[tblMSX_server_vendor_managed] AS Target
-- USING ' + QUOTENAME(@msx_server_name) + '.' + QUOTENAME(DB_NAME()) + '.[dbo].[tblMSX_server_vendor_managed] AS Source
--	ON Target.vnd_id = Source.vnd_id

-- WHEN MATCHED AND NOT (
--		Target.vnd_server_name = Source.vnd_server_name
--	) THEN
--		UPDATE SET	Target.vnd_server_name = Source.vnd_server_name
--WHEN NOT MATCHED BY TARGET THEN
--	INSERT (vnd_id, vnd_server_name)
--	VALUES (Source.vnd_id, Source.vnd_server_name)
--WHEN NOT MATCHED BY SOURCE THEN
--	DELETE;

--SET IDENTITY_INSERT [dbo].[tblMSX_server_vendor_managed] OFF'
--	EXEC		(@sSQL)

--	SELECT		@sSQL = ';MERGE [dbo].[tblMSX_server_sql_info] AS Target
-- USING ' + QUOTENAME(@msx_server_name) + '.' + QUOTENAME(DB_NAME()) + '.[dbo].[tblMSX_server_sql_info] AS Source
--	ON Target.sql_ssd_id = Source.sql_ssd_id

-- WHEN MATCHED AND NOT (
--		Target.sql_last_update = Source.sql_last_update
--		AND ISNULL(Target.sql_centraldba_db_added, ''1/1/1900'') = ISNULL(Source.sql_centraldba_db_added, ''1/1/1900'')
--		AND ISNULL(Target.sql_master_jobs_added, ''1/1/1900'') = ISNULL(Source.sql_master_jobs_added, ''1/1/1900'')
--		AND ISNULL(Target.sql_description, SPACE(0)) = ISNULL(Source.sql_description, SPACE(0))
--		AND ISNULL(Target.sql_is_msx_instance, 0) = ISNULL(Source.sql_is_msx_instance, 0)
--		AND ISNULL(Target.sql_engine_edition, -999) = ISNULL(Source.sql_engine_edition, -999)
--		AND ISNULL(Target.sql_edition_id, -999) = ISNULL(Source.sql_edition_id, -999)
--		AND ISNULL(Target.sql_product_version, SPACE(0)) = ISNULL(Source.sql_product_version, SPACE(0))
--		AND ISNULL(Target.sql_product_level, SPACE(0)) = ISNULL(Source.sql_product_level, SPACE(0))
--		AND ISNULL(Target.sql_has_master_jobs, 0) = ISNULL(Source.sql_has_master_jobs, 0)
--		AND ISNULL(Target.sql_has_local_jobs, 0) = ISNULL(Source.sql_has_local_jobs, 0)
--		AND ISNULL(Target.sql_backup_directory, SPACE(0)) = ISNULL(Source.sql_backup_directory, SPACE(0))
--		AND ISNULL(Target.sql_is_mixed_mode, 0) = ISNULL(Source.sql_is_mixed_mode, 0)
--		AND ISNULL(Target.sql_has_centraldba_db, 0) = ISNULL(Source.sql_has_centraldba_db, 0)
--	) THEN
--		UPDATE SET	Target.sql_last_update = Source.sql_last_update,
--					Target.sql_centraldba_db_added = Source.sql_centraldba_db_added,
--					Target.sql_master_jobs_added = Source.sql_master_jobs_added,
--					Target.sql_description = Source.sql_description,
--					Target.sql_is_msx_instance = Source.sql_is_msx_instance,
--					Target.sql_engine_edition = Source.sql_engine_edition,
--					Target.sql_edition_id = Source.sql_edition_id,
--					Target.sql_product_version = Source.sql_product_version,
--					Target.sql_product_level = Source.sql_product_level,
--					Target.sql_has_master_jobs = Source.sql_has_master_jobs,
--					Target.sql_has_local_jobs = Source.sql_has_local_jobs,
--					Target.sql_backup_directory = Source.sql_backup_directory,
--					Target.sql_is_mixed_mode = Source.sql_is_mixed_mode,
--					Target.sql_has_centraldba_db = Source.sql_has_centraldba_db
--WHEN NOT MATCHED BY TARGET THEN
--	INSERT (sql_ssd_id, sql_last_update, sql_centraldba_db_added, sql_master_jobs_added, 
--			sql_description, sql_is_msx_instance, sql_engine_edition, sql_edition_id,
--			sql_product_version, sql_product_level, sql_has_master_jobs, sql_has_local_jobs,
--			sql_backup_directory, sql_is_mixed_mode, sql_has_centraldba_db)
--	VALUES (Source.sql_ssd_id, Source.sql_last_update, Source.sql_centraldba_db_added, Source.sql_master_jobs_added, 
--			Source.sql_description, Source.sql_is_msx_instance, Source.sql_engine_edition, Source.sql_edition_id,
--			Source.sql_product_version, Source.sql_product_level, Source.sql_has_master_jobs, Source.sql_has_local_jobs,
--			Source.sql_backup_directory, Source.sql_is_mixed_mode, Source.sql_has_centraldba_db)
--WHEN NOT MATCHED BY SOURCE THEN
--	DELETE;'
--	EXEC		(@sSQL)
--  END

SET NOCOUNT OFF